DROP TABLE IF EXISTS jms_dm.dm_cusc_entity_customer_network_province_kg_summary_month_dt;
CREATE TABLE jms_dm.dm_cusc_entity_customer_network_province_kg_summary_month_dt
(
    customer_agent_code    varchar(100) COMMENT '客户代理区code',
    customer_network_code  varchar(100) COMMENT '客户网点code',
    customer_code          varchar(100) COMMENT '客户code',
    customer_entity_code   varchar(100) COMMENT '客户实体code',
    customer_entity_name   varchar(100) COMMENT '客户实体name',
    customer_network_name  varchar(100) COMMENT '客户网点name',
    customer_agent_name    varchar(100) COMMENT '客户代理区name',
    customer_virt_code     varchar(100) COMMENT '客户虚拟代理区code',
    customer_virt_name     varchar(100) COMMENT '客户虚拟代理区name',
    customer_name          varchar(100) COMMENT '客户name',
    receiver_province_id   varchar(100) COMMENT '签收省份id',
    receiver_province_name varchar(100) COMMENT '签收省份name',
    kg_part                decimal(8, 1) COMMENT '重量段',
    kg_part_two            varchar(100) COMMENT '重量段文字',
    weight_total           decimal(8, 2) COMMENT '总重量',
    waybill_num            int COMMENT '揽收量',
    customer_waybill_num   int COMMENT '客户揽收量',
    entity_waybill_num     int COMMENT '客户实体揽收量',
    date_time              date COMMENT '日期',
    update_time            varchar(100) comment '更新时间'
) ENGINE=OLAP
DUPLICATE KEY(customer_agent_code,customer_network_code,customer_code,customer_entity_code)
COMMENT '客渠-客户重量段流向分析'
PARTITION BY RANGE(date_time)
(START ('2023-11-01') END ('2024-01-10') EVERY (INTERVAL 1 day) )
DISTRIBUTED BY HASH(customer_agent_code) BUCKETS 20
PROPERTIES (
'replication_num' = '3',
'dynamic_partition.enable' = 'true',
'dynamic_partition.time_unit' = 'Day',
'dynamic_partition.time_zone' = 'Asia/Shanghai',
'dynamic_partition.start' = '-365',
'dynamic_partition.end' = '3',
'dynamic_partition.prefix' = 'p',
'dynamic_partition.buckets' = '12',
'in_memory' = 'false',
'storage_format' = 'V2'
);
